﻿// =================================================================== 
// 项目说明
//====================================================================
// 文件： ERP_PersonStockDetail.cs
// 创建时间：2016/12/3
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CRMMis.Model;
using CRMMis.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  个人库存详情表.
    /// </summary>
    public partial class ERP_PersonStockDetailDAL : IERP_PersonStockDetailDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取ERP_PersonStockDetail表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的

		private const string QueryDataString = @" select a.*,b.DetailId,b.UserId,
c.Supplyprice,c.SupplyId,
d.CargoCode,d.CargoName,d.ByName,d.Spec,d.Spell,d.Unit,d.Area,d.ProduceName,d.Version,
(select SupplyName from ERP_Supply where SupplyID=c.SupplyID )  SupplyName,
(select TYPE_NAME from ERP_Type where TYPE_ID=d.TypeId )  TypeName,
(select Use_NAME from ERP_Use where Use_ID=d.UseId )  UseNAME,
(c.Supplyprice * a.Count) TotalPrice
from ERP_PersonStockDetail a 
inner join ERP_PersonStock b on a.StoreId = b.StoreId
inner join  ERP_Cargo_Detail c on b.DetailId=c.DetailId
inner join ERP_Cargo d on c.CargoId=d.CargoId ";

        private const string BaseQuery = @"select a.*,b.DetailId,b.UserId,
c.CargoName,c.Spec,c.Version,c.Area,c.ProduceName,c.Supplyprice,
c.CusCargoName,c.CusSpec,c.CusVersion,c.CusArea,c.CusProduceName,c.CusSupplyprice,
c.CustomerId,c.Sos_No,c.Spell,c.ByName,c.CargoCode
from ERP_PersonStockDetail a LEFT
JOIN ERP_PersonStock b ON a.StoreId=b.StoreId
LEFT JOIN V_CargoRelation c ON b.DetailId=c.OwerDetailId
WHERE c.state=1";

        public DataSet GetCusStockList(string sWhere, string sOder)
        {
            string TableString2 = "select * from ("+ BaseQuery + ") SQLA";

            if (sWhere.Length > 0)
                TableString2 += " where " + sWhere;

            if (sOder.Length > 0)
                TableString2 += " order by " + sOder;

            WriteLog("ERP_PersonStockDetailDAL", "GetCusStockList(string sWhere, string sOder)", TableString2);

            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, TableString2);
        }

        /// <summary>
        /// 获取锁定的物品数量
        /// </summary>
        /// <param name="itemId"></param>
        /// <returns></returns>
        public string GetLockCargo(CommonLib.Model.DBTransaction dbTrans, int itemId, int needCount)
        {
            string sql = string.Format(@"select Count SUMCount from ERP_PersonStockDetail where ItemId = {0}  ;
select ISNULL(SUM(Count),0) LockCount
 from (
select a.ItemId,a.Count,b.LendNo  BussNo,'销售' BussType,b.State BussState from ERP_Lend_Detail a
inner join ERP_Lend b on b.LendId = a.LendId 
where b.State<2 and ItemId={0}
union all
select a.ItemId,a.Count,b.PerReturnNo BussNo,'退货' BussType,b.State  from ERP_PerReStoreDetail a
inner join ERP_PerReStore b on b.PerReturnId = a.PerReturnId
where b.State<2 and ItemId={0}
) tab1 ", itemId);
            DataSet rdt = SqlHelper.ExecuteDataset(dbTrans.sql_trans, CommandType.Text, sql);
            int SUMCount = Convert.ToInt32(rdt.Tables[0].Rows[0]["SUMCount"]);
            int LockCount = Convert.ToInt32(rdt.Tables[1].Rows[0]["LockCount"]);
            //string BatchNo = row["BatchNo"].ToString();
            //string CargoName = row["CargoName"].ToString();
            if (needCount > (SUMCount - LockCount))
            {
                string msg = string.Format("总数{0}个,占用数量{1}个", SUMCount, LockCount);
                return msg;
            }
            return null;

        }
    }
}
